package cn.com.callback

import cn.com.elite.EliteBean
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

/**
 * @Title: autoGetUserInfo* @Description: 定时调用，将客户信息表中的数据汇总至customer中
 * @author: tian @date 2021-12-28 14:00:00
 */

class autoSyncCustomerData implements CallBackInvoke {
    private static final Log log = LogFactory.getLog(autoSyncCustomerData.class);

    public void doCallBack(EliteBean eliteBean, String callBack, Map map, String body) {

        long startTime = System.currentTimeMillis();

        String dealSQL = "";
        int insertRow = 0;
        try {

            dealSQL = '''Update Customer R1, Dws.Paqh_Userinfo R2
                       Set R1.Customername         = R2.Investorname,
                           R1.Customer_Id          = R2.Investorid,
                           R1.Customername         = R2.Investorname,
                           R1.Idtype               = R2.Identifiedcardtype,
                           R1.Customerid           = R2.Identifiedcardno,
                           R1.Valid_Documents_Date = R2.Idcardvalidityend,
                           R1.Gender               = R2.Sex,
                           R1.Customertype2_Id     = R2.Investortype,
                           R1.Address              = R2.Address,
                           R1.Mobile               = R2.Mobile,
                           R1.Evaluate             = R2.Riskbearingcapacity,
                           R1.Customer_State       = R2.freezestatus,
                           R1.Account_State        = R2.Isactive,
                           R1.Open_Account         = R2.Opendate,
                           R1.Register_Time        = R2.f_Register_Time,
                           R1.Recommend            = R2.f_Referee_No,
                           R1.Recommend_Name       = R2.f_Referee_Naem,
                           R1.Peak_Value_Qy        = R2.Max_End_Rights,
                           R1.Opengz               = R2.Opengz,
                           R1.Openyy               = R2.Openyy,
                           R1.Open20hj             = R2.Open20hj,
                           R1.Ifopeni              = R2.Ifopeni,
                           R1.Ifopenta             = R2.Ifopenta,
                           R1.Openqq               = R2.Openqq,
                           R1.Opendinedate         = R2.Opendinedate,
                           R1.Opendlrly            = R2.Opendlrly,
                           R1.Opengjt              = R2.Opengjt,
                           R1.Openzly              = R2.Openzly,
                           R1.channel              = R2.f_channel,
                           R1.f_Xf_Channel         = R2.f_Xf_Channel,
                           R1.departmentid         = R2.Departmentid,
                           R1.departmentname       = R2.Departmentname,
                           R1.intermediary         = R2.f_ywry_code,
                           R1.departmentname       = R2.Departmentname,
                           R1.first_rj_time        = R2.fundinday,
                           R1.first_jy_time        = R2.firstday,
                           R1.end_jy_time          = R2.lastday,
                           R1.handling_fee         = R2.commmodelname,
                           R1.margin               = R2.marginmodelname,
                           R1.memo                 = R2.memo,
                           R1.sum_day              = R2.sum_day,
                           R1.sum_number           = R2.sum_number,
                           R1.MODIFIEDBY           = '000116',
                           R1.MODIFIEDDATE         = now(),
                           R1.canceldate           =R2.canceldate,
                           R1.province             =R2.province,
                           R1.city                 =R2.city,
                           R1.dridentityid         =R2.dridentityid,
                           R1.validity_date        =R2.validity_date
                     Where R1.Mobile = R2.Mobile
                     and (R1.Customer_Id is null or R1.Customer_Id='')
                     ''';
            insertRow = eliteBean.update(dealSQL, "dataSource");
            log.debug("通过手机同步已成功新增客户资料:" + insertRow);
            dealSQL = '''Update Customer R1, Dws.Paqh_Userinfo R2
                       Set R1.Customername         = R2.Investorname,
                           R1.Idtype               = R2.Identifiedcardtype,
                           R1.Customerid           = R2.Identifiedcardno,
                           R1.Valid_Documents_Date = R2.Idcardvalidityend,
                           R1.Gender               = R2.Sex,
                           R1.Customertype2_Id     = R2.Investortype,
                           R1.Address              = R2.Address,
                           R1.Mobile               = R2.Mobile,
                           R1.Evaluate             = R2.Riskbearingcapacity,
                           R1.Customer_State       = R2.freezestatus,
                           R1.Account_State        = R2.Isactive,
                           R1.Open_Account         = R2.Opendate,
                           R1.Register_Time        = R2.f_Register_Time,
                           R1.Recommend            = R2.f_Referee_No,
                           R1.Recommend_Name       = R2.f_Referee_Naem,
                           R1.Peak_Value_Qy        = R2.Max_End_Rights,
                           R1.Opengz               = R2.Opengz,
                           R1.Openyy               = R2.Openyy,
                           R1.Open20hj             = R2.Open20hj,
                           R1.Ifopeni              = R2.Ifopeni,
                           R1.Ifopenta             = R2.Ifopenta,
                           R1.Openqq               = R2.Openqq,
                           R1.Opendinedate         = R2.Opendinedate,
                           R1.Opendlrly            = R2.Opendlrly,
                           R1.Opengjt              = R2.Opengjt,
                           R1.Openzly              = R2.Openzly,
                           R1.channel              = R2.f_channel,
                           R1.f_Xf_Channel         = R2.f_Xf_Channel,
                           R1.departmentid         = R2.Departmentid,
                           R1.departmentname       = R2.Departmentname,
                           R1.intermediary         = R2.f_ywry_code,
                           R1.first_rj_time        = R2.fundinday,
                           R1.first_jy_time        = R2.firstday,
                           R1.end_jy_time          = R2.lastday,
                           R1.handling_fee         = R2.commmodelname,
                           R1.margin               = R2.marginmodelname,
                           R1.memo                 = R2.memo,
                           R1.sum_day              = R2.sum_day,
                           R1.sum_number           = R2.sum_number,
                           R1.MODIFIEDBY           = '000116',
                           R1.MODIFIEDDATE         = now(),
                           R1.canceldate           =R2.canceldate,
                           R1.province             =R2.province,
                           R1.city                 =R2.city,
                           R1.dridentityid         =R2.dridentityid,
                           R1.validity_date        =R2.validity_date
                     Where R1.Customer_Id = R2.Investorid''';
            insertRow = eliteBean.update(dealSQL, "dataSource");
            log.debug("通过资金账号同步已成功更新客户资料:" + insertRow);
            dealSQL = '''Insert Into Customer
              (Customer_Guid,
               Customer_Id,
               Customername,
               Idtype,
               Customerid,
               Valid_Documents_Date,
               Gender,
               Customertype2_Id,
               Address,
               Mobile,
               Evaluate,
               Customer_State,
               Account_State,
               Open_Account,
               Register_Time,
               Recommend,
               Recommend_Name,
               f_Xf_Channel,
               Peak_Value_Qy,
               Opengz,
               Openyy,
               Open20hj,
               Ifopeni,
               Ifopenta,
               Openqq,
               Opendinedate,
               Opendlrly,
               Opengjt,
               Openzly,
               Channel,
               departmentid,
               departmentname,
               intermediary,
               first_rj_time,
               first_jy_time,
               end_jy_time,
               handling_fee,
               margin,
               memo,
               sum_day,
               sum_number,
               CREATEDBY,
               CREATEDDATE,
               canceldate,
               province,
               city,dridentityid,validity_date
               )
              Select Distinct Concat(Substr('PA-CUSTOMER-########################', 1, 36 - Length(R1.Investorid)), R1.Investorid) As Customer_Guid,
                              R1.Investorid,
                              R1.Investorname,
                              R1.Identifiedcardtype,
                              R1.Identifiedcardno,
                              R1.Idcardvalidityend,
                              R1.Sex,
                              R1.Investortype,
                              R1.Address,
                              R1.Mobile,
                              R1.Riskbearingcapacity,
                              R1.freezestatus,
                              R1.Isactive,
                              R1.Opendate,
                              R1.f_Register_Time,
                              R1.f_Referee_No,
                              R1.f_Referee_Naem,
                              R1.f_Xf_Channel,
                              R1.Max_End_Rights,
                              R1.Opengz,
                              R1.Openyy,
                              R1.Open20hj,
                              R1.Ifopeni,
                              R1.Ifopenta,
                              R1.Openqq,
                              R1.Opendinedate,
                              R1.Opendlrly,
                              R1.Opengjt,
                              R1.Openzly,
                              R1.f_channel,
                              R1.Departmentid,
                              R1.Departmentname,
                              R1.f_ywry_code,
                              R1.fundinday,
                              R1.firstday,
                              R1.lastday,
                              R1.commmodelname,
                              R1.marginmodelname,
                              R1.memo,
                              R1.sum_day,
                              R1.sum_number,
                              '000116',
                              now(),
                              R1.canceldate,
                              R1.province,
                              R1.city,R1.dridentityid,R1.validity_date
                From Dws.Paqh_Userinfo R1
                Left Join customer R2 On R1.Investorid = R2.Customer_Id left join customer R3 on R1.mobile=r3.MOBILE 
                Where  1=1 and (R2.Customer_Id Is Null or R2.Customer_Id='') and (R3.mobile Is Null or R3.mobile='')''';
            insertRow = eliteBean.update(dealSQL, "dataSource");
            log.debug("已成功新增客户资料:" + insertRow);


            dealSQL = '''Insert Into Customer
              (Customer_Guid,
               Customer_Id,
               Customername,
               Idtype,
               Customerid,
               Valid_Documents_Date,
               Gender,
               Customertype2_Id,
               Address,
               Mobile,
               Evaluate,
               Customer_State,
               Account_State,
               Open_Account,
               Register_Time,
               Recommend,
               Recommend_Name,
               f_Xf_Channel,
               Peak_Value_Qy,
               Opengz,
               Openyy,
               Open20hj,
               Ifopeni,
               Ifopenta,
               Openqq,
               Opendinedate,
               Opendlrly,
               Opengjt,
               Openzly,
               Channel,
               departmentid,
               departmentname,
               intermediary,
               first_rj_time,
               first_jy_time,
               end_jy_time,
               handling_fee,
               margin,
               memo,
               sum_day,
               sum_number,
               CREATEDBY,
               CREATEDDATE,
               canceldate,
               province,
               city,dridentityid,validity_date
               )
              Select UUID(),
                              R1.Investorid,
                              R1.Investorname,
                              R1.Identifiedcardtype,
                              R1.Identifiedcardno,
                              R1.Idcardvalidityend,
                              R1.Sex,
                              R1.Investortype,
                              R1.Address,
                              R1.Mobile,
                              R1.Riskbearingcapacity,
                              R1.freezestatus,
                              R1.Isactive,
                              R1.Opendate,
                              R1.f_Register_Time,
                              R1.f_Referee_No,
                              R1.f_Referee_Naem,
                              R1.f_Xf_Channel,
                              R1.Max_End_Rights,
                              R1.Opengz,
                              R1.Openyy,
                              R1.Open20hj,
                              R1.Ifopeni,
                              R1.Ifopenta,
                              R1.Openqq,
                              R1.Opendinedate,
                              R1.Opendlrly,
                              R1.Opengjt,
                              R1.Openzly,
                              R1.f_channel,
                              R1.Departmentid,
                              R1.Departmentname,
                              R1.f_ywry_code,
                              R1.fundinday,
                              R1.firstday,
                              R1.lastday,
                              R1.commmodelname,
                              R1.marginmodelname,
                              R1.memo,
                              R1.sum_day,
                              R1.sum_number,
                              '000116',
                              now(),
                              R1.canceldate,
                              R1.province,
                              R1.city,R1.dridentityid,R1.validity_date
                From Dws.Paqh_Userinfo R1
                Left Join customer R2 On R1.Investorid = R2.Customer_Id
                Where  1=1 and (R2.Customer_Id Is Null or R2.Customer_Id='')
                And R1.opendate=DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY),'%Y%m%d')
                 ''';
            insertRow = eliteBean.update(dealSQL, "dataSource");
            log.debug("[以资金账号关联同步]已成功新增客户资料:" + insertRow);

            //开户任务
            String TaskSQL1 = '''INSERT INTO account_information(rec_id, task_guid, customerid, task_type, source, createtime, deal_flag) SELECT uuid(),uuid(),s.investorid,'DX_01','2',now(),'0' FROM dws.openname s left join account_information a on a.customerid=s.investorid and a.task_type='DX_01' WHERE  (a.customerid is null or a.customerid='') order by s.opendate desc''';
            int taskRow1 = eliteBean.update(TaskSQL1, "dataSource");
            log.debug("[新增开户名单]：" + taskRow1);

            String updateDepartmentSql="update dws.t_department t,c_codetables c set c.code_id=t.departmentid,c.PCode_ID=t.parentdeptid,c.Code_Name=t.departmentname where  c.use_type = '渠道类型' and binary  c.Code_ID= binary t.departmentid";
            insertRow = eliteBean.update(updateDepartmentSql, "dataSource");
            log.debug("已成功更新营业部:" + insertRow);

            String insertDepartmentSql="INSERT INTO c_codetables (Code_Guid, Code_ID, PCode_ID, Code_Name, Use_Type, Use_Status, Createdby, Createddate, Modifiedby, Modifieddate) select uuid(),departmentid,parentdeptid,departmentname,'渠道类型','1','SELITE',now(),'SELITE',now() from dws.t_department t left join c_codetables c on binary t.departmentid= binary c.Code_ID and c.use_type = '渠道类型'  where c.Code_ID is null ";
            insertRow = eliteBean.update(insertDepartmentSql, "dataSource");
            log.debug("已成功新增营业部:" + insertRow);




            long endTime = System.currentTimeMillis();
            log.debug("autoSyncCustomerData同步客户信息数据-总耗时:" + (endTime-startTime));
            log.debug("===========================================每天同步更新经营客户经理===========================================================");
            startTime = System.currentTimeMillis();
            //查找对应资金账号下发表
            String JlSql='''SELECT c.customer_id,elitetask_guid,s.pa_userid,t.f_distribute_id,e.OBJECTIVE_GUID,c.customer_Guid
               FROM elitetask e 
               LEFT JOIN customer c ON e.customer_guid = c.customer_guid 
               left join dws.t_bfs_cust_staff_relations t on c.customer_id=t.investorid 
               left join staff s on e.HANDLEBY_ID=s.STAFF_ID 
               WHERE  e.map_id = 'M4F19B' 
               and e.CURSTEP_ID_MAP!='J4920T' 
               and s.pa_userid !=t.f_distribute_id''';
            List jlList=eliteBean.chaxun_sql(JlSql,"dataSource");
            log.debug("t_bfs_cust_staff_relations表数据数量:"+jlList.size());
            if (jlList.size()>0){
                for (int i = 0; i < jlList.size(); i++) {
                    Map JLMap = jlList.get(i);
                    String OBJECTIVE_GUID=JLMap.get("OBJECTIVE_GUID");
                    String elitetask_guid=JLMap.get("elitetask_guid");
                    String f_distribute_id=JLMap.get("f_distribute_id");//综合管理平台ID
                    String pa_userid=JLMap.get("pa_userid");//pa_userid
                    String customer_Guid=JLMap.get("customer_Guid");//pa_userid
                    if (OBJECTIVE_GUID!=''&&elitetask_guid!=''&&f_distribute_id!=''){
                        List staffList=eliteBean.chaxun_sql("select staff_id  from staff where pa_userid='"+f_distribute_id+"' limit 1  ","dataSource");
                        if (staffList.size()>0){
                            String staff_id=staffList.get(0).get("staff_id");
                            log.debug("【f_distribute_id:"+pa_userid+"pa_userid:"+pa_userid+"elitetask_guid"+elitetask_guid+"staff_id:"+staff_id+"】");
                            //更新
                            String[] custTaskUpdateSQL = new String[3];
                            //1.更新 elitetask表Hanleby_ID,Handlegroup_Id
                            custTaskUpdateSQL[0] = "Update elitetask  Set Handleby_Id='"+staff_id+"',Handlegroup_Id='JN54X1'   Where elitetask_guid='" + elitetask_guid + "' and MAP_ID='M4F19B'";
                            //2.更新 objective表Hanleby_ID,Handlegroup_Id
                            custTaskUpdateSQL[1] = "Update objective Set Handleby_Id='"+staff_id+"',Handlegroup_Id='JN54X1'     Where  OBJECTIVE_GUID='" + OBJECTIVE_GUID + "' and MAP_ID='M4F19B'";
                            //写入顾问分配历史表
                            custTaskUpdateSQL[2] = "insert into hst_Service_hislist (sys_guid,customer_guid,used_staffid,apply_time,MODIFIEDBY,context,Reviewer_staffid,Reviewer_time)values(UUID(),'" + customer_Guid + "','" + staff_id + "',now(),'" + 'SELITE' + "','底表经营人员更换','SELITE',now())";
                            int[] batchSQLUpdateResult = eliteBean.updateBatch(custTaskUpdateSQL, "dataSource");
                            log.debug("[更换人员更新结果集返回]:"+batchSQLUpdateResult);

                        }
                    }
                }
            }
            endTime = System.currentTimeMillis();
            log.debug("经营更新人员-总耗时:" + (endTime-startTime));

            //注册时间同步更新  2022/09/30   taohao
            List registList =eliteBean.chaxun_sql("select c.mobile ,c.register_time,ym.f_register_time from customer  c \n" +
                    "LEFT JOIN dwd.yun_member ym on ym.f_phone=c.mobile\n" +
                    "where (register_time is null or register_time='')\n" +
                    "and  ym.f_register_time is not null ","dataSource");

            if (registList.size()>0){
                for (int r=0;r<registList.size();r++){
                    Map reMap = registList.get(r);
                    String mobile=reMap.get("mobile");
                    String f_register_time=reMap.get("f_register_time");
                    eliteBean.update("update customer set register_time='"+f_register_time+"' where mobile ='"+mobile+"'","dataSource");
                    log.debug("注册时间更新手机号："+mobile+"=====注册时间："+f_register_time);
                }
            }

            // 删除Customer 表重复数据  2022/11/21   th
            List repeatCustomer=eliteBean.chaxun_sql("select customer_id from customer where (customer_id !='' and  customer_id is not null ) GROUP BY customeR_id HAVING COUNT(1)>1","dataSource");
            if (repeatCustomer.size()>0){

                long repeatCustomerStarttime=System.currentTimeMillis();
                for (int i = 0;i<repeatCustomer.size();i++){
                    Map repeatCustomerMap = repeatCustomer.get(i);
                    String Customer_id=repeatCustomerMap.get("customer_id");
                    log.debug("重复的customerid:"+Customer_id);
                    List SelectCustomer=eliteBean.chaxun_sql("select customer_guid from customer where customer_id='"+Customer_id+"' ORDER BY appointment_time DESC limit 1 ","dataSource");
                    String customer_guid=SelectCustomer.get(0).get("customer_guid");
                    //开始更换customer_guid
                    String[] custTaskUpdateSQL = new String[9];
                    custTaskUpdateSQL[0]="Update elitetask e set e.customer_guid='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER c  WHERE c.customer_guid=e.customer_guid and c.CUSTOMER_GUID!='"+customer_guid+"'AND c.CUSTOMER_ID='"+Customer_id+"')";

                    custTaskUpdateSQL[1]="Update objective o set o.customer_guid='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER c WHERE  c.customer_guid=o.customer_guid and c.CUSTOMER_GUID!='"+customer_guid+"'AND c.CUSTOMER_ID='"+Customer_id+"')";

                    custTaskUpdateSQL[2]="Update event e  set e.customer_guid='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER c WHERE c.customer_guid=e.customer_guid and c.CUSTOMER_GUID!='"+customer_guid+"'AND c.CUSTOMER_ID='"+Customer_id+"')";

                    custTaskUpdateSQL[3]="Update eventcallnature e  set e.customer_guid='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER  c WHERE c.customer_guid=e.customer_guid and c.CUSTOMER_GUID!='"+customer_guid+"'AND c.CUSTOMER_ID='"+Customer_id+"')";

                    custTaskUpdateSQL[4]="Update record r set r.customer_guid='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER c  WHERE c.customer_guid=r.customer_guid and c.CUSTOMER_GUID!='"+customer_guid+"' AND c.CUSTOMER_ID='"+Customer_id+"')";

                    custTaskUpdateSQL[5]="Update assign_his a  set a.customer_guid='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER c  WHERE c.customer_guid=a.customer_guid and c.CUSTOMER_GUID!='"+customer_guid+"'AND c.CUSTOMER_ID='"+Customer_id+"')";

                    custTaskUpdateSQL[6]="Update hst_Service_hislist h  set h.customer_guid='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER c  WHERE c.customer_guid=h.customer_guid and c.CUSTOMER_GUID!='"+customer_guid+"'AND c.CUSTOMER_ID='"+Customer_id+"')";

                    custTaskUpdateSQL[7]="Update labelhandle_his h  set h.RELATION_GUID='"+customer_guid+"' WHERE EXISTS (SELECT c.CUSTOMER_GUID FROM CUSTOMER c  WHERE c.customer_guid=h.RELATION_GUID and c.CUSTOMER_GUID!='"+customer_guid+"'AND c.CUSTOMER_ID='"+Customer_id+"')";


                    //删除customer 表
                    custTaskUpdateSQL[8]="DELETE FROM CUSTOMER WHERE CUSTOMER_GUID!='"+customer_guid+"' AND CUSTOMER_ID='"+Customer_id+"'";
                    int [] batchSQLUpdateResult = eliteBean.updateBatch(custTaskUpdateSQL, "dataSource");

                    log.debug("[customer表重复数据更新删除结果集返回]:"+batchSQLUpdateResult);

                }
                long repeatCustomerEndtime=System.currentTimeMillis();
                log.debug("customer表重复数据更新删除-总耗时(毫秒):" + (repeatCustomerEndtime-repeatCustomerStarttime));
            }



        } catch (Exception e) {
            log.error("同步平安数据到过河兵异常!", e);
        }
    }
}